package com.iweb.dao;

import com.common.model.Page;
import com.iweb.pojo.Admin;
import com.iweb.pojo.User;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao{

    private Connection conn;
    private PreparedStatement pst;
    private ResultSet rs;

    public UserDaoImpl(Connection conn) {
        this.conn = conn;
    }

    @Override
    public User findById(Serializable user_id) throws Exception {
        User user = null;
        String sql = "select * from tb_user where user_id = ?";
        pst = conn.prepareStatement(sql);
        pst.setObject(1,user_id);
        rs = pst.executeQuery();
        while (rs.next()){
            user = createInstance(rs);
        }
        return user;
    }

    @Override
    public User findByUsername(String user_name) throws Exception {
        User user = null;
        String sql = "select * from tb_user where user_name = ?";
        pst=conn.prepareStatement(sql);
        pst.setObject(1,user_name);
        rs = pst.executeQuery();

        while (rs.next()){
            user = createInstance(rs);
        }
        return user;
    }
    @Override
    public void save(User user) throws Exception {
        String sql = "insert into tb_user (user_id,user_name,user_password,user_phone) values (?,?,?,?)";

        pst = conn.prepareStatement(sql);
        pst.setString(1,user.getUser_id());
        pst.setString(2,user.getUser_name());
        pst.setString(3,user.getUser_password());
        pst.setString(4,user.getUser_phone());
        pst.executeUpdate();
    }


    @Override
    public void deleteById(Serializable id) throws Exception {
        String sql = "delete from tb_user where user_id = ?";
        pst = conn.prepareStatement(sql);
        pst.setObject(1,id);
        pst.executeUpdate();
    }

    @Override
    public List<User> findAll(Page page) throws Exception {
        List<User> list = new ArrayList<>();
        String sql = "SELECT * FROM tb_user LIMIT ?,?";
        pst = conn.prepareCall(sql);

        pst.setInt(1,(page.getPage()-1)*page.getLimit());
        pst.setInt(2,page.getLimit());

        rs = pst.executeQuery();
        User user = null;
        while (rs.next()){
            user = createInstance(rs);
            list.add(user);
        }

        return list;
    }

    @Override
    public long selectCount() throws Exception {
        String sql = "select count(1) from tb_user";
        pst = conn.prepareStatement(sql);
        rs = pst.executeQuery();
        rs.next();
        return rs.getInt(1);
    }

    private User createInstance(ResultSet rs) throws SQLException {
        User user = new User();
        user.setUser_id(rs.getString("user_id"));
        user.setUser_name(rs.getString("user_name"));
        user.setUser_password(rs.getString("user_password"));
        user.setUser_phone(rs.getString("user_phone"));
        user.setUser_email(rs.getString("user_email"));
        user.setUser_question1(rs.getString("user_question1"));
        user.setUser_question2(rs.getString("user_question2"));
        return user;
    }

    /*杨遂萍新增*/

    @Override
    public void saveByPhone(User user) throws Exception {
        String sql = "insert into tb_user (user_id,user_phone,user_password) values (?,?,?)";
        pst = conn.prepareStatement(sql);
        pst.setString(1,user.getUser_id());
        pst.setString(2,user.getUser_phone());
        pst.setString(3,user.getUser_password());
        pst.executeUpdate();
    }

    @Override
    public User findUserByPhone(String user_phone) throws Exception {
        User user = null;
        // sql语句
        String sql= "select * from tb_user where user_phone = ?";
        // 预编译sql语句
        pst = conn.prepareStatement(sql);
        // 设置参数
        pst.setString(1,user_phone);
        // 查询表，返回查询结果集
        rs = pst.executeQuery();
        // 遍历结果集
        while(rs.next()){
            // 获取一个admin的对象
            user = createInstance(rs);
        }
        return user;
    }

    @Override
    public User findUser(String type, String account) throws Exception {
        User user = null;
        // sql语句
        StringBuilder baseSql=new StringBuilder("select * from tb_user where") ;
        if(type.equals("user_name")){
            baseSql.append(" user_name = ?");//将条件附在 sql的基本部分后面
        }
        if(type.equals("user_phone")){
            baseSql.append(" user_phone = ?");//将条件附在 sql的基本部分后面
        }
        if(type.equals("user_email")){
            baseSql.append(" user_email = ?");//将条件附在 sql的基本部分后面
        }
        String sql = baseSql.toString();
        // 预编译sql语句
        pst = conn.prepareStatement(sql);
        // 设置参数
        pst.setString(1,account);
        // 查询表，返回查询结果集
        rs = pst.executeQuery();
        // 遍历结果集
        while(rs.next()){
            // 获取一个admin的对象
            user = createInstance(rs);
        }
        return user;
    }
}
